package com.app.TCES.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.app.TCES.domain.Class_Course;
import com.app.TCES.domain.Department;
import com.app.TCES.domain.ST_indicators;
import com.app.TCES.domain.TT_indicators;
import com.app.TCES.domain.Teacher_Course;
import com.app.TCES.domain.Test;

@Mapper
public interface Teacher_Mapper {

	// 查找课程及上课班级(条件:tchId)
	@Select("select * from teacher t,class cl,course co,class_course cc where t.tchId=cc.tchId and co.courseId=cc.courseId and cl.classId=cc.classId and t.tchId=#{tchId}")
	List<Class_Course> selectT_CourseBy_tchId(@Param("tchId") Object object);

	// 查找教师所在院系(条件:tchId)
	@Select("select d.deptId,d.deptName from teacher t,department d where t.deptId=d.deptId and t.tchId=#{tchId}")
	Department selectDept(@Param("tchId") Object object);

	// 通过教师所在院系查找同院系下的所有教师(条件:deptId 出当前ID)
	@Select("select * from teacher t,department d,teacher_course tc,course c where t.deptId=d.deptId and t.tchId=tc.tchId and c.courseId=tc.courseId and d.deptId=#{deptId} and t.tchId!=#{tchId}")
	List<Teacher_Course> selectTeacherBy_deptId(@Param("deptId") int deptId, @Param("tchId") Object object);

	// 获取教师同行评价问题内容
	@Select("select * from test where testType=1")
	List<Test> selectTeacherTest();

	// 教师同行评分
	@Insert("insert into TT_indicators(teacherId,tchId,score) values(#{teacherId},#{tchId},#{score})")
	void insertTT(@Param("teacherId") int teacherId, @Param("tchId") Object object, @Param("score") double score);

	// 通过教师查找已评价过的同行数据(条件:tchId)
	@Select("select * from TT_indicators where tchId=#{tchId}")
	List<TT_indicators> selectIndicators(@Param("tchId") Object object);

	// 查看分数(学生评分)
	@Select("select avg(score) from ST_indicators where teacherId=#{tchId}")
	double selectScores_S(@Param("tchId") Object object);

	// 查看分数(同行评分)
	@Select("select avg(score) from TT_indicators where teacherId=#{tchId}")
	double selectScores_T(@Param("tchId") Object object);

	// 所有教师的分数(学生)(平均值)
	@Select("SELECT a.tchId,a.tchName,a.b FROM (select t.tchId,t.tchName,avg(st.score) as b from teacher t, ST_indicators st WHERE t.tchId=st.teacherId group by st.teacherId) a")
	List<ST_indicators> selectST_score_avg();

	// 查询平均分大于80的教师总数(学生)
	@Select("SELECT COUNT(*) FROM (select t.tchId,t.tchName,avg(st.score) as b from teacher t, ST_indicators st WHERE t.tchId=st.teacherId group by st.teacherId) a where a.b>80")
	int SA(); // 优

	// 查询平均分大于60小于80的教师总数(学生)
	@Select("SELECT COUNT(*) FROM (select t.tchId,t.tchName,avg(st.score) as b from teacher t, ST_indicators st WHERE t.tchId=st.teacherId group by st.teacherId) a where a.b<80 and a.b>60")
	int SB(); // 良

	// 查询平均分小于60的教师总数(学生)
	@Select("SELECT COUNT(*) FROM (select t.tchId,t.tchName,avg(st.score) as b from teacher t, ST_indicators st WHERE t.tchId=st.teacherId group by st.teacherId) a where a.b<60")
	int SC();

	// 查询平均分大于80的教师总数(学生)
	@Select("SELECT COUNT(*) FROM (select t.tchId,t.tchName,avg(st.score) as b from teacher t, TT_indicators st WHERE t.tchId=st.teacherId group by st.teacherId) a where a.b>80")
	int TA(); // 优

	// 查询平均分大于60小于80的教师总数(学生)
	@Select("SELECT COUNT(*) FROM (select t.tchId,t.tchName,avg(st.score) as b from teacher t, TT_indicators st WHERE t.tchId=st.teacherId group by st.teacherId) a where a.b<80 and a.b>60")
	int TB(); // 良

	// 查询平均分小于60的教师总数(学生)
	@Select("SELECT COUNT(*) FROM (select t.tchId,t.tchName,avg(st.score) as b from teacher t, TT_indicators st WHERE t.tchId=st.teacherId group by st.teacherId) a where a.b<60")
	int TC();

	// 所有教师的分数(同行)(平均值)
	@Select("SELECT a.tchId,a.tchName,a.b FROM (select t.tchId,t.tchName,avg(st.score) as b from teacher t, TT_indicators st WHERE t.tchId=st.teacherId group by st.teacherId) a")
	List<TT_indicators> selectTT_score_avg();
	
	
}
